package dbaccess;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import services.Service;
import user.User;

import entities.Provider;
import entities.ServiceProvider;
import entities.Subnet;
import entities.VM;

class BddView {
	private Connection connection = null;

	/**
	 * Se connecte a la base de donnée. Leve une exception si echec
	 * @throws ClassNotFoundException
	 * @throws SQLException 
	 */
	protected void connectBdd() throws ClassNotFoundException, SQLException{

		Class.forName("org.sqlite.JDBC");
		this.connection = DriverManager.getConnection("jdbc:sqlite:becloud.db");
		this.connection.setAutoCommit(true);

	}

	/**
	 * Se deconnecte de la bdd (optionnel). Leve une exception si probleme.
	 * @throws SQLException 
	 */
	protected void disconnectBdd() throws SQLException{
		this.connection.close();
	}

	private String braquet(String name){
		return ("'"+name+"'");
	}

	protected void addUser(String login, String password, String role,String provider) throws SQLException{
		Statement stmt = this.connection.createStatement();
		String sql = "INSERT INTO USER (Login,Password,NameRole,Provider) " +
				"VALUES ("+"'"+login+"'"+" , "+"'"+password+"'"+" , "+"'"+role+"'"+" , "+this.braquet(provider)+" );" ;
		stmt.executeUpdate(sql);
		stmt.close();
	}

	protected void addUser(String login, String password, String role, String email, String provider) throws SQLException{
		Statement stmt = this.connection.createStatement();
		String sql = "INSERT INTO USER (Login,Password,NameRole,Email,Provider) " +
				"VALUES ("+"'"+login+"'"+" , "+"'"+password+"'"+" , "+"'"+role+"'"+" , "+"'"+email+"' ,"+this.braquet(provider)+");" ;
		stmt.executeUpdate(sql);
		stmt.close();

	}

	protected String getPasswordUser(String login, String userOrProvider) throws SQLException {
		String resultat = null;
		Statement stmt = this.connection.createStatement();
		String sql;
		
		if(userOrProvider.equalsIgnoreCase("User")){
			sql = "SELECT Password FROM USER " +
					"WHERE USER.login = "+this.braquet(login) +";";
		}else{
			sql = "SELECT Password FROM PROVIDER " +
					"WHERE PROVIDER.Name = "+this.braquet(login) +";";
		}
		ResultSet rs = stmt.executeQuery(sql);
		resultat = rs.getString("Password");
		rs.close();
		stmt.close();

		return resultat;
	}

	protected ArrayList<VM> getVMFromUser(String login) throws SQLException{
		ArrayList<VM> resultat = new ArrayList<VM>();

		Statement stmt = this.connection.createStatement();
		String sql = "SELECT * FROM VM " +
				"WHERE VM.Login = "+this.braquet(login) +";";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next()){
			resultat.add(new VM(rs.getInt("VMID"), rs.getString("Nodename"), rs.getInt("rightID"), rs.getString("TemplateOuOS"),
					rs.getInt("HDD"),rs.getInt("RAM"), rs.getInt("SWAP"), rs.getInt("CPU"), rs.getString("IP_Adress"),
					rs.getString("Name"), rs.getString("State"), rs.getString("Login"), rs.getInt("SubnetID"), rs.getInt("VMProviderID")));
		}
		rs.close();
		stmt.close();

		return resultat;
	}

	protected String getRole(String login) throws SQLException {
		String resultat = null;

		Statement stmt = this.connection.createStatement();
		String sql = "SELECT NameRole FROM USER " +
				"WHERE USER.Login = "+this.braquet(login) +";";
		ResultSet rs = stmt.executeQuery(sql);
		resultat = rs.getString("NameRole");
		rs.close();
		stmt.close();

		return resultat;
	}

	protected ArrayList<Subnet> getSubnetFromUser(String login) throws SQLException {
		ArrayList<Subnet> resultat = new ArrayList<Subnet>(); 

		Statement stmt = this.connection.createStatement();
		String sql = "SELECT * FROM SUBNET " +
				"WHERE SUBNET.Login = "+this.braquet(login) +";";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next()){
			resultat.add(new Subnet(rs.getInt("SubnetID"), rs.getInt("NumberOfVM"), rs.getString("Name"), rs.getString("Login")));
		}
		rs.close();
		stmt.close();

		return resultat;
	}

	protected ArrayList<Provider> getProviderList() throws SQLException {
		ArrayList<Provider> resultat = new ArrayList<Provider>(); 

		Statement stmt = this.connection.createStatement();
		String sql = "SELECT * FROM PROVIDER ;";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next()){
			resultat.add(new Provider(rs.getString("Name"), rs.getString("API_URL"),rs.getString("Email"),rs.getString("password")));
		}
		rs.close();
		stmt.close();

		return resultat;
	}

	protected ArrayList<String> getTemplate(String providerName) throws SQLException {
		ArrayList<String> resultat = new ArrayList<String>(); 

		Statement stmt = this.connection.createStatement();
		String sql = "SELECT NameTemplate FROM TEMPLATE "+
				"WHERE TEMPLATE.NameProvider = "+this.braquet(providerName)+";";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next()){
			resultat.add(rs.getString("NameTemplate"));
		}
		rs.close();
		stmt.close();


		return resultat;
	}

	protected ArrayList<String> getUserRoles(String providerName) throws SQLException {
		ArrayList<String> resultat = new ArrayList<String>(); 

		Statement stmt = this.connection.createStatement();
		String sql = "SELECT NameRole FROM ROLE "+
				"WHERE ROLE.NameProvider = "+this.braquet(providerName)+";";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next()){
			resultat.add(rs.getString("NameRole"));
		}
		rs.close();
		stmt.close();

		return resultat;
	}

	protected ArrayList<ServiceProvider> getServices(String providerName) throws SQLException {
		ArrayList<ServiceProvider> resultat = new ArrayList<ServiceProvider>(); 

		Statement stmt = this.connection.createStatement();
		String sql = "SELECT * FROM SERVICE_PROVIDER "+
				"WHERE SERVICE_PROVIDER.NameProvider = "+this.braquet(providerName)+";";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next()){
			resultat.add(new ServiceProvider(rs.getString("NameService"), rs.getString("PageAssociee"), rs.getString("NameProvider")));
		}
		rs.close();
		stmt.close();

		return resultat;
	}

	protected void addService(String providerName, ArrayList<String> listeDeService) throws SQLException {
		for(String s : listeDeService){
			//ajouter les services pour un provider
			Statement stmt = this.connection.createStatement();
			String sql = "INSERT INTO SERVICE_PROVIDER (NameService, NameProvider) " +
					"VALUES ("+this.braquet(s)+" , "+this.braquet(providerName)+" );" ;
			stmt.executeUpdate(sql);
			stmt.close();

			//ajouter les services tout court, si ils y sont pas deja
			try{
				Statement stmt2 = this.connection.createStatement();
				String sql2 = "INSERT INTO SERVICE (NameService) " +
						"VALUES ("+this.braquet(s)+" );" ;
				stmt2.executeUpdate(sql2);
				stmt2.close();
			}catch(SQLException e){
			}
		} 


	}

	protected void saveVM(String login, String vmName, String vmIp, String vmTemplate, int vmProviderID) throws SQLException {
		Statement stmt = this.connection.createStatement();
		String sql = "INSERT INTO VM (Login,Name,IP_Adress, TemplateOuOS, State, VMProviderID) " +
				"VALUES ("+this.braquet(login)+" , "+this.braquet(vmName)+" , "+this.braquet(vmIp)+" , "+this.braquet(vmTemplate)+" , "+this.braquet("OFF")+" , "+vmProviderID+" );" ;
		stmt.executeUpdate(sql);
		stmt.close();

	}

	protected void saveVM(String login, String vmName, String vmIp, String vmOs,
			int vmRam, int vmCpu, int vmHdd, String nodeName, int swap, int vmProviderID) throws SQLException {

		Statement stmt = this.connection.createStatement();
		String sql = "INSERT INTO VM (Login,Name,IP_Adress, TemplateOuOS, State,RAM,CPU,HDD,NodeName,SWAP,VMProviderID) " +
				"VALUES ("+this.braquet(login)+" , "+this.braquet(vmName)+" , "+this.braquet(vmIp)+" , "+this.braquet(vmOs)+" , "+
				this.braquet("OFF")+" , "+vmRam+" , "+vmCpu+" ,"+vmHdd+" , "+this.braquet(nodeName)+" , "+swap+" ,"+vmProviderID+" );" ;
		stmt.executeUpdate(sql);
		stmt.close();


	}

	protected void addProvider(String name,String email, String aPI_URL, String password) throws SQLException {
		Statement stmt = this.connection.createStatement();
		String sql = "INSERT INTO PROVIDER (Name,API_URL,Email, Password) " +
				"VALUES ("+this.braquet(name)+" , "+this.braquet(aPI_URL)+" , "+this.braquet(email)+" ,"+this.braquet(password)+");" ;
		stmt.executeUpdate(sql);
		stmt.close();		
	}

	protected void addTemplate(String name, String s) throws SQLException {
		Statement stmt = this.connection.createStatement();
		String sql = "INSERT INTO TEMPLATE (NameProvider,NameTemplate) " +
				"VALUES ("+this.braquet(name)+" , "+this.braquet(s)+" );" ;
		stmt.executeUpdate(sql);
		stmt.close();		
	}

	protected void addRole(String name, String s) throws SQLException {
		Statement stmt = this.connection.createStatement();
		String sql = "INSERT INTO ROLE (NameRole,NameProvider) " +
				"VALUES ("+this.braquet(s)+" , "+this.braquet(name)+" );" ;
		stmt.executeUpdate(sql);
		stmt.close();		
	}

	protected Connection getConnection() {
		return connection;
	}

	protected void addService(String serviceName) throws SQLException {
		Statement stmt = this.connection.createStatement();
		String sql = "INSERT INTO SERVICE (NameService) " +
				"VALUES ("+this.braquet(serviceName)+" );" ;
		stmt.executeUpdate(sql);
		stmt.close();		
	}

	protected void addVMInSubnet(int vmID, int subnetID) throws SQLException {
		Statement stmt = this.connection.createStatement();
		String sql = "UPDATE VM "+"SET SubnetID = "+subnetID+" WHERE VMID ="+vmID+";";
		stmt.executeUpdate(sql);
		stmt.close();		
	}

	protected void setVmState(int vmID, String state) throws SQLException {
		Statement stmt = this.connection.createStatement();
		String sql = "UPDATE VM "+"SET State = "+this.braquet(state)+" WHERE VMID ="+vmID+";";
		stmt.executeUpdate(sql);
		stmt.close();			
	}

	protected String getVmState(int vmid) throws SQLException {
		Statement stmt = this.connection.createStatement();
		String sql = "SELECT State FROM VM "+
				"WHERE VM.VMID = "+vmid+";";
		ResultSet rs = stmt.executeQuery(sql);
		String res = rs.getString("State");
		rs.close();
		stmt.close();		

		return res;
	}

	protected ArrayList<String> getService() throws SQLException {
		ArrayList<String> resultat = new ArrayList<String>(); 

		Statement stmt = this.connection.createStatement();
		String sql = "SELECT * FROM SERVICE ";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next()){
			resultat.add(rs.getString("NameService"));
		}
		rs.close();
		stmt.close();

		return resultat;
	}

	public void addCarac(String serviceName, String typeOfCarac, String min, String max,String selected) throws SQLException {
		Statement stmt = this.connection.createStatement();
		String sql = "INSERT INTO CARAC_SERVICE (NameService,Type,Min,Max,Selected) " +
				"VALUES ("+this.braquet(serviceName)+", "+this.braquet(typeOfCarac)+" , "+this.braquet(min)+" , "+this.braquet(max)+" , "+this.braquet(selected)+");" ;
		stmt.executeUpdate(sql);
		stmt.close();	
	}

	public String getProviderMail(String providerName) throws SQLException {
		Statement stmt = this.connection.createStatement();
		String sql = "SELECT * FROM PROVIDER "+
				"WHERE PROVIDER.Name = "+this.braquet(providerName)+";";
		ResultSet rs = stmt.executeQuery(sql);

		return rs.getString("Email");
	}

	public void link(String login1, String login2) throws SQLException {
		Statement stmt = this.connection.createStatement();
		String sql = "INSERT INTO LINK_USER (Login1,Login2) " +
				"VALUES ("+this.braquet(login1)+", "+this.braquet(login2)+");" ;
		stmt.executeUpdate(sql);
		stmt.close();		
	}

}
